{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd,xlwings as xw,订单完成_函数 as dd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "wb=xw.Book(r'C:\\Users\\UUPT\\Desktop\\应付工作\\9月\\9月加减款\\2024年9月加减款汇总表-9.30.xlsx')\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {},
   "outputs": [],
   "source": [
    "#直营加减款\n",
    "df1=wb.sheets('9.30').range('W3:AT146').options(pd.DataFrame).value.reset_index()\n",
    "df1=df1.iloc[0:] #因为是合并单元格表头，所以从第二行开始取"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {},
   "outputs": [],
   "source": [
    "#代理加减款\n",
    "df2=wb.sheets('9.30').range('AU3:BR146').options(pd.DataFrame).value.reset_index()\n",
    "df2=df2.iloc[0:] #因为是合并单元格表头，所以从第二行开始取"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "metadata": {},
   "outputs": [],
   "source": [
    "#跑男汇总\n",
    "df3=wb.sheets('9.30').range('b3:l233').options(pd.DataFrame).value.reset_index()\n",
    "df3=df3.iloc[0:] #因为是合并单元格表头，所以从第二行开始取"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "metadata": {},
   "outputs": [],
   "source": [
    "#用户汇总\n",
    "df4=wb.sheets('9.30').range('m3:v233').options(pd.DataFrame).value.reset_index()\n",
    "df4=df4.iloc[0:] #因为是合并单元格表头，所以从第二行开始取"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "metadata": {},
   "outputs": [],
   "source": [
    "直营跑男提现=dd.通用凭证模版(dd.clm,'应付凭证',摘要='9.30支付'+df1['部门']+df1['业务类型']+'跑男提现',科目编码='224101',借贷方向='借方',本币=df1['跑男提现'],\n",
    "               部门编码=df1['部门编码'],部门=df1['部门']) \n",
    "直营跑男补贴=dd.通用凭证模版(dd.clm,'应付凭证',摘要='9.30支付'+df1['部门']+df1['业务类型']+'跑男补贴',科目编码='660154',借贷方向='借方',本币=df1['补贴跑男'],\n",
    "               部门编码=df1['部门编码'],部门=df1['部门'],项目编码=df1['部门编码'],项目=df1['部门'])   "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "metadata": {},
   "outputs": [],
   "source": [
    "直营用户退款=dd.通用凭证模版(dd.clm,'应付凭证',摘要='9.30支付'+df1['部门']+df1['业务类型']+'用户退款',科目编码='224117',借贷方向='借方',本币=df1['用户退款']) \n",
    "直营用户补贴=dd.通用凭证模版(dd.clm,'应付凭证',摘要='9.30支付'+df1['部门']+df1['业务类型']+'用户补贴',科目编码='660153',借贷方向='借方',本币=df1['补贴用户'],\n",
    "               部门编码=df1['部门编码'],部门=df1['部门'],项目编码=df1['部门编码'],项目=df1['部门']) "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "metadata": {},
   "outputs": [],
   "source": [
    "代理跑男提现=dd.通用凭证模版(dd.clm,'应付凭证',摘要='9.30支付'+df2['往来单位']+df2['业务类型']+'跑男提现',科目编码=' 22413002',借贷方向='借方',本币=df2['跑男提现'],\n",
    "               往来单位编码=df2['往来单位编码'],往来单位=df2['往来单位']) \n",
    "代理跑男补贴=dd.通用凭证模版(dd.clm,'应付凭证',摘要='9.30支付'+df2['往来单位']+df2['业务类型']+'跑男补贴',科目编码=' 22413003',借贷方向='借方',本币=df2['补贴跑男'],\n",
    "               往来单位编码=df2['往来单位编码'],往来单位=df2['往来单位']) "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "metadata": {},
   "outputs": [],
   "source": [
    "代理用户退款=dd.通用凭证模版(dd.clm,'应付凭证',摘要='9.30支付'+df2['往来单位']+df2['业务类型']+'用户退款',科目编码=' 22413001',借贷方向='借方',本币=df2['用户退款']) \n",
    "代理用户补贴=dd.通用凭证模版(dd.clm,'应付凭证',摘要='9.30支付'+df2['往来单位']+df2['业务类型']+'用户补贴',科目编码=' 22413001',借贷方向='借方',本币=df2['补贴用户']) "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "metadata": {},
   "outputs": [],
   "source": [
    "#银行\n",
    "#跑男\n",
    "一dianmingle02合计=dd.通用凭证模版(dd.clm,'应付凭证',摘要='9.30支付跑男提现及补贴',科目编码='101223',借贷方向='贷方',本币=[df3['dianmingle02'].sum()])\n",
    "\n",
    "一招商银行合计=dd.通用凭证模版(dd.clm,'应付凭证',摘要='9.30支付跑男提现及补贴',科目编码='100207',借贷方向='贷方',本币=[df3['招商银行'].sum()])\n",
    "\n",
    "一郑好办支付宝合计=dd.通用凭证模版(dd.clm,'应付凭证',摘要='9.30支付跑男提现及补贴',科目编码='101222',借贷方向='贷方',本币=[df3['郑好办支付宝'].sum()])\n",
    "\n",
    "一银联合计=dd.通用凭证模版(dd.clm,'应付凭证',摘要='9.30支付跑男提现及补贴',科目编码='101219',借贷方向='贷方',本币=[df3['银联'].sum()])\n",
    "#用户\n",
    "二dianmingle02合计=dd.通用凭证模版(dd.clm,'应付凭证',摘要='9.30支付用户提现及补贴',科目编码='101223',借贷方向='贷方',本币=[df4['dianmingle02'].sum()])\n",
    "\n",
    "二招商银行合计=dd.通用凭证模版(dd.clm,'应付凭证',摘要='9.30支付用户提现及补贴',科目编码='100207',借贷方向='贷方',本币=[df4['招商银行'].sum()])\n",
    "\n",
    "二郑好办支付宝合计=dd.通用凭证模版(dd.clm,'应付凭证',摘要='9.30支付用户提现及补贴',科目编码='101222',借贷方向='贷方',本币=[df4['郑好办支付宝'].sum()])\n",
    "\n",
    "二银联合计=dd.通用凭证模版(dd.clm,'应付凭证',摘要='9.30支付用户提现及补贴',科目编码='101219',借贷方向='贷方',本币=[df4['银联'].sum()])\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "metadata": {},
   "outputs": [],
   "source": [
    "凭证=pd.concat([直营跑男提现,直营跑男补贴,直营用户退款,直营用户补贴,代理跑男提现,代理跑男补贴,代理用户退款,代理用户补贴,\n",
    "              一dianmingle02合计,一招商银行合计,一郑好办支付宝合计,一银联合计,二dianmingle02合计,二招商银行合计,二郑好办支付宝合计,二银联合计]) "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "metadata": {},
   "outputs": [],
   "source": [
    "凭证.to_clipboard()"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "base",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.11.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
